package com.elock.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.elock.base.core.Page;
import com.elock.base.core.Pageable;
import com.elock.base.core.dao.impl.DaoCenter;
import com.elock.base.core.util.ConvertUtil;
import com.elock.base.core.util.WebUtils;

/**
 * Dao - 终端事件信息
 * @author 仁德
 */
@Repository("terminalMessageEventDao")
public class TerminalMessageEventDao extends DaoCenter {

	public Page<Map<String, Object>> findPage(String elockids, String terminalids,
			Long lastId, String plateNum, String terminalNum, String elockNum,
			Long saleOrgId, String startTime, String endTime, Pageable pageable) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select m.*,p.status_desc,p.vol,p.jzdw,p.altitude,p.speed,p.direction,p.gps_time,");
		sql.append("ec.berth,ec.position,ec.car_number,wm.warning_message");
		sql.append(" from terminal_message_event m");
		sql.append(" left join terminal t on m.terminal_num = t.terminal_num");
		sql.append(
				" left join elock e on m.elock_num = e.elock_num and m.company_info_id=e.company_info_id");
		sql.append(" left join terminal_position p on m.uuid = p.uuid");
		sql.append(
				" left join elock_car ec on ec.elock_num = m.elock_num and m.company_info_id=ec.company_info_id");
		sql.append(" left join warning_message wm on wm.uuid = m.uuid");
		sql.append(" where 1 = 1");
		if (companyInfoId != null) {
			sql.append(" and m.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(elockids) && !ConvertUtil.isEmpty(terminalids)) {
			sql.append(
					" and (e.id in (" + elockids + ") or t.id in (" + terminalids + "))");
		}
		else if (!ConvertUtil.isEmpty(elockids)) {
			sql.append(" and e.id in (" + elockids + ")");
		}
		else if (!ConvertUtil.isEmpty(terminalids)) {
			sql.append(" and t.id in (" + terminalids + ")");
		}
		if (lastId != null) {
			sql.append(" and m.id > ?");
			list.add(lastId);
		}
		if (!ConvertUtil.isEmpty(plateNum)) {
			sql.append(" and ec.car_number like ?");
			list.add("%" + plateNum + "%");
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and m.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(elockNum)) {
			sql.append(" and m.elock_num like ?");
			list.add("%" + elockNum + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and m.event_time >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and m.event_time < ?");
			list.add(endTime + " 24:00:00");
		}
		if (saleOrgId != null) {
			sql.append(
					" and t.terminal_num in (select terminal_num from terminal_sale_org where sale_org_id = ?)");
			list.add(saleOrgId);
		}
//		else {
//			sql.append(
//					" and exists (select 1 from terminal_sale_org where terminal_id=t.id and sale_org_id in ("
//							+ saleOrgBaseService.saleOrgAuth()
//							+ "))");
//		}
		sql.append(" order by m.id desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		Page<Map<String, Object>> page = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return page;
	}

	public List<Map<String, Object>> findList(String elockids, String terminalids,
			Long lastId, Integer maxcount) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select m.*,p.status_desc,p.vol,p.jzdw,p.altitude,p.speed,p.direction,p.gps_time,");
		sql.append("ec.berth,ec.position,ec.car_number,wm.warning_message");
		sql.append(" from terminal_message_event m");
		sql.append(" left join terminal t on m.terminal_num = t.terminal_num");
		sql.append(
				" left join elock e on m.elock_num = e.elock_num and m.company_info_id=e.company_info_id");
		sql.append(" left join terminal_position p on m.uuid = p.uuid");
		sql.append(
				" left join elock_car ec on ec.elock_num = m.elock_num and m.company_info_id=ec.company_info_id");
		sql.append(" left join warning_message wm on wm.uuid = m.uuid");
		sql.append(" where 1 = 1");
		if (companyInfoId != null) {
			sql.append(" and m.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(elockids) && !ConvertUtil.isEmpty(terminalids)) {
			sql.append(
					" and (e.id in (" + elockids + ") or t.id in (" + terminalids + "))");
		}
		else if (!ConvertUtil.isEmpty(elockids)) {
			sql.append(" and e.id in (" + elockids + ")");
		}
		else if (!ConvertUtil.isEmpty(terminalids)) {
			sql.append(" and t.id in (" + terminalids + ")");
		}
		if (lastId != null) {
			sql.append(" and m.id > ?");
			list.add(lastId);
		}
		sql.append(" order by m.id desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		List<Map<String, Object>> mlist = getNativeDao().findListMap(sql.toString(),
				objs,
				maxcount == null ? 0 : maxcount);

		return mlist;
	}

	public List<Map<String, Object>> getStatus(String carIds, String elockIds,
			String terminalIds) {

		String carSql = "";
		String elockSql = "";
		String terminalSql = "";
		if (!ConvertUtil.isEmpty(carIds)) {
			carIds = carIds.substring(1);
			carSql = "select id,1 as type,ifnull(e.status,0) status from car c left join"
					+ " (select car_id, ers.status from elock_realtime_status ers, elock_car ec"
					+ " where ers.elock_id=ec.elock_id and ers.status = 1 and ec.car_id in ("
					+ carIds
					+ ") group by ec.car_id) e"
					+ " on c.id = e.car_id where c.id in ("
					+ carIds
					+ ") and exists (select 1 from elock_car where car_id = c.id)";
		}
		if (!ConvertUtil.isEmpty(elockIds)) {
			elockIds = elockIds.substring(1);
			elockSql = "select elock_id id,2 as type,status from elock_realtime_status where elock_id in ("
					+ elockIds
					+ ")";
		}
		if (!ConvertUtil.isEmpty(terminalIds)) {
			terminalIds = terminalIds.substring(1);
			terminalSql = "select terminal_id id,3 as type,is_online status from terminal_online where terminal_id in ("
					+ terminalIds
					+ ")";
		}
		String sql = "";
		if (!ConvertUtil.isEmpty(carSql)
				&& !ConvertUtil.isEmpty(elockSql)
				&& !ConvertUtil.isEmpty(terminalSql)) {
			sql = carSql + " union all " + elockSql + " union all " + terminalSql;
		}
		else if (!ConvertUtil.isEmpty(elockSql) && !ConvertUtil.isEmpty(terminalSql)) {
			sql = elockSql + " union all " + terminalSql;
		}
		else if (!ConvertUtil.isEmpty(carSql) && !ConvertUtil.isEmpty(terminalSql)) {
			sql = carSql + " union all " + terminalSql;
		}
		else if (!ConvertUtil.isEmpty(carSql) && !ConvertUtil.isEmpty(elockSql)) {
			sql = carSql + " union all " + elockSql;
		}
		else if (!ConvertUtil.isEmpty(carSql)) {
			sql = carSql;
		}
		else if (!ConvertUtil.isEmpty(elockSql)) {
			sql = elockSql;
		}
		else if (!ConvertUtil.isEmpty(terminalSql)) {
			sql = terminalSql;
		}
		List<Map<String, Object>> list = getNativeDao().findListMap(sql, null, 0);

		return list;
	}

	public Map<String, Object> getRealtimePosition(Long id, Integer type,
			String terminalNum) {

		Map<String, Object> map = new HashMap<String, Object>();
		String sql = "";
		if (type == 1) {
			sql = "select ers.* from elock_realtime_status ers, elock_car ec where ers.elock_id=ec.elock_id and ec.car_id=?";
			List<Map<String, Object>> carList = getNativeDao().findListMap(sql,
					new Object[] { id },
					0);
			for (int i = 0; i < carList.size(); i++) {
				Map<String, Object> carMap = carList.get(i);
				int status = Integer.parseInt(carMap.get("status").toString());
				if (status == 1) {
					map = carMap;
					break;
				}
				if (i == carList.size() - 1 && map.isEmpty()) {
					map = carMap;
				}
			}
		}
		else if (type == 2) {
			sql = "select * from elock_realtime_status ers where ers.elock_id=?";
			Map<String, Object> elockMap = getNativeDao().findSingleMap(sql,
					new Object[] { id });
			if (elockMap != null) {
				map = elockMap;
			}
		}
		else if (type == 3) {
			sql = "select r.*,tl.is_online,t.sim from terminal_online tl,terminal_realtime_position r,terminal t where tl.terminal_id=r.terminal_id and t.id=tl.terminal_id";
			Object[] objs = null;
			if (id != null) {
				sql += " and tl.terminal_id=?";
				objs = new Object[] { id };
			}
			else {
				sql += " and tl.terminal_num=?";
				objs = new Object[] { terminalNum };
			}
			Map<String, Object> terminalMap = getNativeDao().findSingleMap(sql, objs);
			if (terminalMap != null) {
				map = terminalMap;
			}
		}
		return map;
	}

	public Page<Map<String, Object>> findCarMonitorPage(Integer type, Long oid,
			String carNumber, String startTime, String endTime, Pageable pageable) {
		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select tme.* from terminal_message_event tme");
		sql.append(" left join car c on c.car_number=tme.plate_num");
		sql.append(" left join car_sale_org cso on cso.car=c.id");
		sql.append(
				" left join sale_org so on so.id=cso.sale_org where tme.plate_num is not null");
		if (companyInfoId != null) {
			sql.append(" and tme.company_info_id=?");
			list.add(companyInfoId);
		}
		if (oid != null) {
			sql.append(" and so.id=?");
			list.add(oid);
		}
		if (!ConvertUtil.isEmpty(carNumber)) {
			sql.append(" and tme.plate_num like ?");
			list.add("%" + carNumber + "%");
		}

		sql.append(" order by tme.create_date desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		Page<Map<String, Object>> getPage = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return getPage;
	}

	public Page<Map<String, Object>> findTerminalMonitorPage(Integer type, Long oid,
			String terminalNum, String startTime, String endTime, Pageable pageable) {
		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select tme.* from terminal_message_event tme");
		sql.append(" left join terminal t on t.terminal_num=tme.terminal_num");
		sql.append(" left join terminal_sale_org tso on tso.terminal_id=t.id");
		sql.append(
				" left join sale_org so on so.id=tso.sale_org_id where tme.terminal_num is not null");
		if (companyInfoId != null) {
			sql.append(" and tme.company_info_id=?");
			list.add(companyInfoId);
		}
		if (oid != null) {
			sql.append(" and so.id=?");
			list.add(oid);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and tme.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}

		sql.append(" order by tme.create_date desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		Page<Map<String, Object>> getPage = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return getPage;
	}

	public List<Map<String, Object>> findListByTaskSn(String taskSn) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select m.*,p.status_desc,p.vol,p.jzdw,p.altitude,p.speed,p.direction,p.gps_time,");
		sql.append("ec.position,ec.berth,ec.car_number,wm.warning_message");
		sql.append(" from terminal_message_event m");
		sql.append(" left join terminal t on m.terminal_num = t.terminal_num");
		sql.append(
				" left join elock e on m.elock_num = e.elock_num and m.company_info_id=e.company_info_id");
		sql.append(" left join terminal_position p on m.uuid = p.uuid");
		sql.append(
				" left join elock_car ec on ec.elock_num = m.elock_num and m.company_info_id=ec.company_info_id");
		sql.append(" left join warning_message wm on wm.uuid = m.uuid");
		sql.append(" where m.task_sn=?");
		list.add(taskSn);
		if (companyInfoId != null) {
			sql.append(" and m.company_info_id=?");
			list.add(companyInfoId);
		}

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		List<Map<String, Object>> mlist = getNativeDao().findListMap(sql.toString(),
				objs,
				0);

		return mlist;
	}

	public Integer countTerminalMessageEvent(String elockids, String terminalids,
			Long lastId, String plateNum, String terminalNum, String elockNum,
			Long saleOrgId, String startTime, String endTime, Pageable pageable,
			Integer page, Integer size) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select count(m.id) from terminal_message_event m");
		sql.append(" left join terminal t on m.terminal_num = t.terminal_num");
		sql.append(
				" left join elock e on m.elock_num = e.elock_num and m.company_info_id=e.company_info_id");
		sql.append(" left join terminal_position p on m.uuid = p.uuid");
		sql.append(
				" left join elock_car ec on ec.elock_num = m.elock_num and m.company_info_id=ec.company_info_id");
		sql.append(" left join warning_message wm on wm.uuid = m.uuid");
		sql.append(" where 1 = 1");
		if (companyInfoId != null) {
			sql.append(" and m.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(elockids) && !ConvertUtil.isEmpty(terminalids)) {
			sql.append(
					" and (e.id in (" + elockids + ") or t.id in (" + terminalids + "))");
		}
		else if (!ConvertUtil.isEmpty(elockids)) {
			sql.append(" and e.id in (" + elockids + ")");
		}
		else if (!ConvertUtil.isEmpty(terminalids)) {
			sql.append(" and t.id in (" + terminalids + ")");
		}
		if (lastId != null) {
			sql.append(" and m.id > ?");
			list.add(lastId);
		}
		if (!ConvertUtil.isEmpty(plateNum)) {
			sql.append(" and ec.car_number like ?");
			list.add("%" + plateNum + "%");
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and m.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(elockNum)) {
			sql.append(" and m.elock_num like ?");
			list.add("%" + elockNum + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and m.event_time >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and m.event_time < ?");
			list.add(endTime + " 24:00:00");
		}
		if (saleOrgId != null) {
			sql.append(
					" and t.terminal_num in (select terminal_num from terminal_sale_org where sale_org_id = ?)");
			list.add(saleOrgId);
		}
//		else {
//			sql.append(
//					" and exists (select 1 from terminal_sale_org where terminal_id=t.id and sale_org_id in ("
//							+ saleOrgBaseService.saleOrgAuth()
//							+ "))");
//		}
		sql.append(" order by m.create_date desc, m.id desc");

		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}
		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}
		Integer count = getNativeDao().findInt(sql.toString(), objs);

		return count;
	}

	public List<Map<String, Object>> findTerminalMessageEventList(String elockids,
			String terminalids, Long lastId, String plateNum, String terminalNum,
			String elockNum, Long saleOrgId, String startTime, String endTime,
			Pageable pageable, Integer page, Integer size) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select m.*,p.status_desc,p.vol,p.jzdw,p.altitude,p.speed,p.direction,p.gps_time,");
		sql.append("ec.berth,ec.position,ec.car_number,wm.warning_message");
		sql.append(" from terminal_message_event m");
		sql.append(" left join terminal t on m.terminal_num = t.terminal_num");
		sql.append(
				" left join elock e on m.elock_num = e.elock_num and m.company_info_id=e.company_info_id");
		sql.append(" left join terminal_position p on m.uuid = p.uuid");
		sql.append(
				" left join elock_car ec on ec.elock_num = m.elock_num and m.company_info_id=ec.company_info_id");
		sql.append(" left join warning_message wm on wm.uuid = m.uuid");
		sql.append(" where 1 = 1");
		if (companyInfoId != null) {
			sql.append(" and m.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(elockids) && !ConvertUtil.isEmpty(terminalids)) {
			sql.append(
					" and (e.id in (" + elockids + ") or t.id in (" + terminalids + "))");
		}
		else if (!ConvertUtil.isEmpty(elockids)) {
			sql.append(" and e.id in (" + elockids + ")");
		}
		else if (!ConvertUtil.isEmpty(terminalids)) {
			sql.append(" and t.id in (" + terminalids + ")");
		}
		if (lastId != null) {
			sql.append(" and m.id > ?");
			list.add(lastId);
		}
		if (!ConvertUtil.isEmpty(plateNum)) {
			sql.append(" and ec.car_number like ?");
			list.add("%" + plateNum + "%");
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and m.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(elockNum)) {
			sql.append(" and m.elock_num like ?");
			list.add("%" + elockNum + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and m.event_time >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and m.event_time < ?");
			list.add(endTime + " 24:00:00");
		}
		if (saleOrgId != null) {
			sql.append(
					" and t.terminal_num in (select terminal_num from terminal_sale_org where sale_org_id = ?)");
			list.add(saleOrgId);
		}
//		else {
//			sql.append(
//					" and exists (select 1 from terminal_sale_org where terminal_id=t.id and sale_org_id in ("
//							+ saleOrgBaseService.saleOrgAuth()
//							+ "))");
//		}
		sql.append(" order by m.create_date desc, m.id desc");

		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				objs,
				0);

		return maps;
	}
}